package com.siqi.hr.system.common.dao.mysql;

import com.siqi.hr.common.entity.ExportEntity;
import com.siqi.hr.common.generator.IdGenerator;
import com.siqi.hr.common.utils.ExportUtils;
import com.siqi.hr.common.utils.TableConstant;
import com.siqi.hr.system.common.chan.FieldChan;
import com.siqi.hr.system.common.dao.intf.ExportImportDao;
import com.siqi.hr.system.common.sql.SField;
import com.siqi.hr.system.common.sql.STable;
import com.siqi.hr.system.common.utils.CustomUtils;
import com.siqi.hr.system.common.utils.DateUtil;
import com.siqi.hr.system.organization.entity.Employee;
import org.apache.commons.collections.map.LinkedMap;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

/**
 * 通用导出dao层实现类
 * @author     xiaogq
 * @date       2017-01-05
 */
@Repository("exportImportDao")
public class ExportImportDaoImpl implements ExportImportDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Map<String, Object> entityExport(ExportEntity exportEntity, Employee emp) {
        Map<String, Object> localmap = new HashMap<String, Object>();
        Map<String, Object> celMap = new LinkedMap();
        List<SField> sf = exportEntity.getFieldChan(exportEntity.getJsonChar(), celMap);
        exportEntity.setFieldChan(new FieldChan(sf));
        String sql = exportEntity.toSqlString().toString();
        List<Map<String, Object>> values = this.jdbcTemplate.queryForList(sql);
        values.add(0, celMap);
        localmap.put("values", values);
        return localmap;
    }

    @Override
    public Map<String, List<Map<String, Object>>> queryQutoFieldId(List<Map<String, Object>> listDatas, List<Map<String, String>> fieldMap) {
        Map<String, List<Map<String, Object>>> idMap = new LinkedMap();
        //存放引用字段索引
        List<String> isPkColumn = new ArrayList<String>();
        //存放引用字段表明
        List<String> tableName = new ArrayList<String>();
        //存放引用字段的字段id
        List<String> fieldIdAndName = new ArrayList<String>();
        //存放外键名称集合
        List<LinkedHashSet<String>> pk_names = new ArrayList<LinkedHashSet<String>>();
        for (Map<String, String> map : fieldMap){
            String fieldName = map.get("fieldName") == null ? "" : map.get("fieldName").trim();
            if (fieldName.startsWith("pk_") || fieldName.endsWith("_singlebox") || fieldName.endsWith("_radio")) {
                isPkColumn.add(map.get("index"));
                tableName.add(CustomUtils.returnTableName(fieldName));
                fieldIdAndName.add(map.get("fieldid"));
                pk_names.add(new LinkedHashSet());
            } else if (fieldName.endsWith("_entity")) {
                isPkColumn.add(map.get("index"));
                tableName.add("sys_custom_entity");
                fieldIdAndName.add(map.get("fieldid"));
                pk_names.add(new LinkedHashSet());
            }
        }

        for (int i = 0; i < listDatas.size(); i++) {
            if (i != 0) {
                Map<String, Object> colMap = listDatas.get(i);
                int p = 0;
                for (Map<String, String> map : fieldMap) {
                    if ((!(map.get("index")).equals("0")) && (isPkColumn.contains(map.get("index")))) {
                        Object value = colMap.get(map.get("index"));
                        if (value != null && !value.equals("")) {
                            pk_names.get(p).add(String.valueOf(value));
                        }
                        p++;
                    }
                }
            }
        }

        idMap = getDatabaseIds(pk_names, isPkColumn, tableName, fieldIdAndName);
        return idMap;
    }

    @Override
    public List<String> getEmpLoginNames(String entityId) {
        String sql = "select job_number_varchar from t_" + entityId;
        List<String> list = jdbcTemplate.queryForList(sql, String.class);
        return list;
    }

    @Override
    public boolean fieldIsUnique(String entityId, String fieldName, String fieldValue) {
        boolean bol = false;
        if (!fieldValue.equals("")) {
            String sql = "select count(id) from t_" + entityId + " where " + fieldName + " = '" + fieldValue + "'";
            int countNum = jdbcTemplate.queryForObject(sql,Integer.class);
            if (countNum > 0) {
                bol = true;
            }
        }
        return bol;
    }

    @Override
    public void batchAddImportDate(List<Map<String, Object>> succesDatas, List<Map<String, String>> fieldMap, String entityId, Employee emp) {
        if (succesDatas.size() > 0){
            //定义存放要批量导入的数据集合
            List<Map<String, Object>> addDatas = new ArrayList<Map<String, Object>>();
            //定义存放实体与员工关系数据集合
            List<Map<String, String>> idList = new ArrayList<Map<String, String>>();
            //获取实体创建人、修改人、拥有者、创建时间、更新时间字段信息
            List<String> commonFieldList = getFieldNameByEntityId(entityId);
            //定义实体类型
            int clientEntityTypeId = 0;
            //获得当前登录系统用户id
            String empId = emp.getId(),deptId = "";
            for (int i = 0; i < succesDatas.size(); i++) {
                //存放要批量导入的map集合
                Map<String,Object> uoMap = new LinkedMap();
                Map<String, String> idMap = new LinkedMap();
                Map<String, Object> datasMap = succesDatas.get(i);
                String id = IdGenerator.createId(Integer.parseInt(entityId)) + i;
                uoMap.put("id", id);
                for (Map fieldmap : fieldMap) {
                    String fieldName = ((String)fieldmap.get("fieldName")).trim();
                    String index = ((String)fieldmap.get("index")).trim();
                    String dataValues = String.valueOf(datasMap.get(index)).trim();
                    if (fieldName.equals("pk_sys_custom_entity_type_1") && StringUtils.isNotBlank(dataValues) && !dataValues.equals("null")) {
                        clientEntityTypeId = Integer.parseInt(dataValues);
                    }else if (fieldName.equals("pk_t_3_3")) {
                        if (StringUtils.isNotBlank(dataValues) && !dataValues.equals("null")) {
                            empId = dataValues;
                        }
                    }else if(fieldName.equals("pk_t_2_1")){
                        if (StringUtils.isNotBlank(dataValues) && !dataValues.equals("null")) {
                            deptId = dataValues;
                        }
                        uoMap.put(fieldName, dataValues);
                    }else {
                        uoMap.put(fieldName, dataValues);
                    }
                }
                if (clientEntityTypeId != 0) {
                    uoMap.put("pk_sys_custom_entity_type_1", clientEntityTypeId);
                }
                for(String fName : commonFieldList){
                    //如果导入的实体有创建时间、更新时间字段
                    if(fName.equals("create_date_datetime") || fName.equals("update_date_timestamp")){
                        uoMap.put(fName, DateUtil.formatDate(new Date(),"yyyy-MM-dd HH:mm:ss"));
                    }else if(fName.equals("pk_t_3_1") || fName.equals("pk_t_3_2")){//如果导入实体有创建人、更新人字段
                        uoMap.put(fName, emp.getId());
                    }else{//如果导入实体有记录拥有者字段
                        uoMap.put(fName, empId);
                    }
                }
                idMap.put("hostId", id);
                idMap.put("deptId",deptId);
                idMap.put("empId", empId);
                idMap.put("entityTypeId", String.valueOf(clientEntityTypeId));
                idList.add(idMap);
                addDatas.add(uoMap);
            }
            batchAddDatas(addDatas,idList,entityId,emp);
        }
    }

    @Override
    public List<String> getFieldNameByEntityId(String entityId) {
        StringBuffer sql = new StringBuffer("select name from sys_custom_field where entity_id = ? and  name in('pk_t_3_1','pk_t_3_2','pk_t_3_3','create_date_datetime','update_date_timestamp') order by position ");
        List<String> fieldList = jdbcTemplate.queryForList(sql.toString(),new Object[] {entityId},String.class);
        return fieldList;
    }

    /**
     * 一次性查询Excel文件中的引用字段的名称查询对应的id
     * @author             xiaogq
     * @param values       引用字段信息
     * @param isPkColumn   引用字段索引
     * @param tableName    引用表名
     * @param fieldId      字段id
     */
    public Map<String, List<Map<String, Object>>> getDatabaseIds(List<LinkedHashSet<String>> values, List<String> isPkColumn, List<String> tableName, List<String> fieldId){
        Map<String, List<Map<String, Object>>> map = new LinkedMap();
        for (int i = 0; i < values.size(); i++) {
            List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
            if (values.get(i).size() > 0) {
                String sql = traverSql(values.get(i),tableName.get(i),fieldId.get(i)).toString();
                result = jdbcTemplate.queryForList(sql);
                map.put(isPkColumn.get(i), result);
            } else {
                map.put(isPkColumn.get(i), result);
            }
        }
        return map;
    }

    /**
     * 根据Excel文件中的引用字段的名称查询对应的id
     * @author             xiaogq
     * @param value        引用字段信息
     * @param tableName    引用表名
     * @param fieldIds     字段id
     * @return
     */
    public static StringBuilder traverSql(LinkedHashSet<String> value, String tableName, String fieldIds){
        StringBuilder sBuilder = new StringBuilder();
        String columName = "", id = "";
        //如果是实体表
        if (tableName.equals("sys_custom_entity")) {
            id = "table_code";
            columName = "inchinese";
        } else if (tableName.equals("sys_custom_entity_type")) {//如果是实体类型表
            id = "id";
            columName = "inchinese";
        } else if(tableName.equals("sys_custom_selectbox")){//如果是枚举字段表
            id = "id";
            columName = "name";
        }else {//如果是客户化表(t表)
            id = "id";
            columName = "name_varchar";
        }
        sBuilder.append(" select " + id + " as id," + columName + " as name from " + tableName + " where " + columName + " in (");
        sBuilder.append("'" + StringUtils.join(value, "','") + "'");
        sBuilder.append(")");
        if (tableName.equals("sys_custom_selectbox")) {
            sBuilder.append(" and field_id = '" + fieldIds + "'");
        }
        return sBuilder;
    }

    /**
     * 导入实体数据时批量保存数据方法
     * @author                 xiaogq
     * @param addDatas         要保存数据
     * @param idList           实体与员工相关关系数据
     * @param entityId         实体id
     * @param emp              当前登录系统用户信息
     */
    public void batchAddDatas(final List<Map<String, Object>> addDatas, List<Map<String, String>> idList, String entityId, Employee emp){
        if (addDatas.size() > 0) {
            StringBuffer bactchSql = new StringBuffer();
            StringBuffer feildSql = new StringBuffer();
            StringBuffer valueSql = new StringBuffer();
            bactchSql.append(" insert into " + STable.TABLE_HEAD + entityId);
            Map<String, Object> sqlMap = addDatas.get(0);
            for (String fieldName : sqlMap.keySet()) {
                feildSql.append("," + fieldName);
                valueSql.append(",?");
            }
            feildSql.deleteCharAt(0);
            valueSql.deleteCharAt(0);
            bactchSql.append(" (" + feildSql + ")").append(" values (" + valueSql + ") ");

            jdbcTemplate.batchUpdate(bactchSql.toString(), new BatchPreparedStatementSetter() {
                public int getBatchSize(){
                    return addDatas.size();
                }
                public void setValues(PreparedStatement ps, int index) throws SQLException {
                    Map<String, Object> map = addDatas.get(index);
                    int i = 0;
                    for (String key : map.keySet()) {
                        i++;
                        String value = String.valueOf(map.get(key)).trim();
                        if (value.equals("")) {
                            value = null;
                        }
                        if (key.endsWith("_bit")) {
                            if (value.equals("1")) {
                                ps.setObject(i,true);
                            }else{
                                ps.setObject(i,false);
                            }
                        }else{
                            ps.setObject(i, value);
                        }
                    }
                }
            });
            //如果导入员工时批量添加门关员工与主部系(xiaogq)
            if(Integer.parseInt(entityId) == TableConstant.EMPLOYEE_TCODE){
                batchAddEmpRelatedDepts(idList);
            }
        }
    }

    /**
     * 导入员工时批量添加门关员工与主部系 deptId
     * @author          xiaogq
     * @param idList
     */
    public void batchAddEmpRelatedDepts(List<Map<String, String>> idList){
        if (idList.size() > 0) {
            String rctableName = TableConstant.Z_EMP_DEPT_TNAME ;
            String[] resql = new String[idList.size()];
            for (int j = 0; j < idList.size(); j++) {
                Map<String, String> idMap = idList.get(j);
                resql[j] = ("insert into " + rctableName + " (host_record_id,related_record_id,type) " + "values ('" + idMap.get("hostId") + "','" + idMap.get("deptId") + "',true)");
            }
            if (resql.length > 0){
                jdbcTemplate.batchUpdate(resql);
            }
        }
    }

}
